Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# UPDATE

The UPDATE command moves analytic workspace changes from a temporary area to the database table in which the workspace is stored. Typically, you use an UPDATE statement when you are finished making changes in a workspace; however, you can also specify UPDATE commands periodically as you go along.

Your changes are not saved until you execute a COMMIT statement, either from Oracle OLAP or from SQL. When you do not use the UPDATE and COMMIT commands, changes made to an analytic workspace during your session are discarded when you end your Oracle session.

Note: You can detach and reattach a workspace without losing updated changes, even though they are not committed. This is because the detaching and reattaching occur within a single database session

Syntax

UPDATE [MULTI [aquired_objects]] [analytic_workspaces]

Arguments

When you do not specify any parameters, the command updates all analytic workspaces that are attached in read/write non-exclusive and read/write exclusive modes and all acquired objects (that is, all acquired variables, relations, valuesets, and dimensions) in all analytic workspaces that are attached in multiwriter mode.

acquired_objects

  A list of the names of acquired objects, separated by commas, in analytic workspaces attached in multiwriter mode. These objects can be any variable, relation, valueset, or dimension that you have acquired using an ACQUIRE statement.

  Important: you cannot update an object when it is dimensioned by an acquired and maintained dimension unless you update that dimension first.

workspaces

  A list of names, separated by commas. of one or more workspaces attached in read/write or multiwriter mode.

Notes

Automatic COMMIT

Many users execute DML statements using SQL*Plus or OLAP Worksheet. Both of these tools automatically execute a COMMIT statement when you end your session.

Triggering Program Execution When UPDATE Executes

Using the TRIGGER command, you can make an UPDATE statement an event that automatically executes an OLAP DML program. See "Trigger Programs" for more information

Shared Workspaces

When you have attached a shared workspace and another user has read/write access, that user's UPDATE and COMMIT commands do not affect your view of the workspace. Your view of the data remains the same as when you attached the workspace. When you want access to the changes, you can detach the workspace and reattach it.

Effect of a ROLLBACK Statement

The OLAP DML does not provide a way to issue a SQL `ROLLBACK` statement; however, you could execute one in your session from outside Oracle OLAP (for example, through PL/SQL). When a `ROLLBACK` statement is executed in your session, Oracle OLAP checks to see whether there are uncommitted updates in an attached workspace.

- When there are uncommitted updates (that is, you have made changes and executed an UPDATE statement, but you have not subsequently executed a COMMIT statement), then Oracle OLAP discards your changes and detaches the workspace.

- When you have no uncommitted updates, then Oracle OLAP takes no action in response to a `ROLLBACK` statement. This means that, when you have not issued an UPDATE statement since your last COMMIT statement, Oracle OLAP takes no action and all your changes remain in the workspace during your session.

When you rollback to a savepoint and there are uncommitted updates that occurred subsequent to the savepoint, Oracle OLAP discards those updates and detaches the workspace. Uncommitted updates that occurred before the savepoint remain in the workspace, and you can see them when you reattach the workspace in the same session.

Examples

Example 24-25 Saving Analytic Workspace Changes

The following statement moves changes in the current workspace session to the database table in which the workspace is stored.

    UPDATE

In order to save the changes in the database, the UPDATE statement must be followed by a COMMIT statement.